//
//  AppDelegate.h
//  Address-book📞
//
//  Created by Mars_ZXB on 16/8/6.
//  Copyright © 2016年 BAT3. All rights reserved.
//


#import "DataBaseHandleContact.h"
#import "SandboxHandler.h"
#import <sqlite3.h>
#import "ModelOfContact.h"
@implementation DataBaseHandleContact

/** 构造器 */
+ (instancetype)sharaDateBaseHandleContact {
    static DataBaseHandleContact *dataBase = nil;
    if (dataBase == nil) {
        dataBase = [[DataBaseHandleContact alloc]init];
    }
    return dataBase;
}
sqlite3 *db = nil;
#pragma mark - SQLite 操作.
/** 打开数据库. API:sqlite3_open */
- (void)openSQLName:(NSString *)name {
    
    // API: sqlite3_open
    
    //判断 db 指针是否为空
    if (db != nil) {
        NSLog(@"数据库已经开启");
        return;
    }

    NSString *path = [SandboxHandler pathForDirectory:Documents WithFileName:name];
    int result = sqlite3_open(path.UTF8String, &db);
    
    // 根据函数返回值, 判断执行是否正确.
    if (result == SQLITE_OK) {
        NSLog(@"数据库开启成功.");
    }
    else {
        NSLog(@"数据库打开失败, code = %d", result);
    }

}

#pragma mark - 创建表格.
/** 创建表格. API:sqlite3_exec */
- (void)createTabel {
    // 创建表格的 SQL语句.
    NSString *sql = @"CREATE  TABLE IF NOT EXISTS dls160101(ID_S INTEGER PRIMARY KEY AUTOINCREMENT, section text, name text, age INTEGER, phoneNumber text, gender text, image blob)";
    int result = sqlite3_exec(db, sql.UTF8String, nil, nil, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"创建表格成功");
    }
    else {
        NSLog(@"创建表格失败, 错误码:%d", result);
    }



}

#pragma mark - 插入数据
/** 插入数据. */
- (void)insertModel:(ModelOfContact *)contact WithSection:(NSString *)section {
    NSData *data = UIImageJPEGRepresentation(contact.image, 1);
    //插入数据的 SQL 语句
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO dls160101 (section, name, age, phoneNumber, gender, image) VALUES ('%@', '%@', '%ld', '%@', '%@', '%@')", section, contact.name, contact.age, contact.phoneNumber, contact.gender, data];
    
    int result = sqlite3_exec(db, sql.UTF8String, nil, nil, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }
    else {
        NSLog(@"插入数据失败, 错误码:%d", result);
    }

}

#pragma mark - 删除数据.
/** 删除数据. API: sqlite3_exec */
- (void)deleteModel:(ModelOfContact *)contact {
    
    // 删除语句.
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM dls160710 WHERE name = '%@' AND phoneNumber = '%@'", contact.name, contact.phoneNumber];
    int result = sqlite3_exec(db, sql.UTF8String, nil, nil, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除数据成功");
    }
    else {
        NSLog(@"删除数据失败, 错误码:%d", result);
    }

}

#pragma mark - 检索数据.
/** 检索数据. API: sqlite3_stmt */
- (NSArray<ModelOfContact *> *)select{
    
    NSMutableArray *array = [NSMutableArray array];
    // 查找 SQL 语句.
    NSString *zSql = @"SELECT * FROM dls160101";
    // 创建一个 准备好的语句对象.
    sqlite3_stmt *stmt = nil;
    
    // 为准备好的语句对象赋值.(SQL 语句内容).
    int result = sqlite3_prepare_v2(db, zSql.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"开始查询");
        int i = 0;
        // 当有下一行数据时, 继续执行检索.
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            i++;
            // 查询条件匹配, 使用函数簇 将需要的列值取出来.
            const unsigned char * section = sqlite3_column_text(stmt, 1);
            const unsigned char * name = sqlite3_column_text(stmt, 2);
            int age = sqlite3_column_int(stmt, 3);
            const unsigned char * phoneNumber = sqlite3_column_text(stmt, 4);
            const unsigned char * gender = sqlite3_column_text(stmt, 5);
          

            // 创建 model 赋值
            ModelOfContact *contact = [[ModelOfContact alloc]init];
            contact.section = [NSString stringWithUTF8String:(const char *)section];
            contact.name = [NSString stringWithUTF8String:(const char *)name];
            contact.phoneNumber = [NSString stringWithUTF8String:(const char *)phoneNumber];
            contact.age = age;
            contact.gender = [NSString stringWithUTF8String:(const char *)gender];

            
            // 添加到数组中.
            [array addObject:contact];
        }
        NSLog(@"查询结束, 有%d条记录匹配", i);
        
    }
    
    else {
        NSLog(@"未能启动查询, 错误码:% d", result);
    }
    
    // 销毁 stmt 对象.(内存管理)
    sqlite3_finalize(stmt);
    
    return array;
    


}

#pragma mark - 关闭数据库.
/** 关闭数据库 API: sqlite3_close */
- (void)closeDB {
    
    int result = sqlite3_close(db);
    
    if (result == SQLITE_OK) {
        NSLog(@"关闭成功");
        
        // sqlite 对象指针指向空.
        db = nil;
    }
    else {
        NSLog(@"关闭失败, 错误码:%d", result);
    }
    
    
}

@end
